if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_receive_schedule_sync')
          and type in ('P','PC'))
   drop procedure bd_proc_receive_schedule_sync
go


create procedure bd_proc_receive_schedule_sync (@bh varchar(max),@ip varchar(200),@operator varchar(200)) as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;

-- 定义返回受影响行数结果
	declare @result int = 0;


	DECLARE @yearMonth varchar(7);

	--  异常扑捉机制
		BEGIN TRY

			CREATE TABLE #returnTable(
				id INT IDENTITY,
				user_serial bigint null,
				user_no varchar(100) COLLATE Chinese_PRC_CI_AS,--工号
				user_name varchar(100) COLLATE Chinese_PRC_CI_AS,--姓名
				msg varchar(1000) COLLATE Chinese_PRC_CI_AS--错误信息
			)
			CREATE TABLE #scheduleTable(
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
				user_no varchar(200) COLLATE Chinese_PRC_CI_AS,
			  fh VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,--id
			  banci_mc VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  date DATE
			)

			CREATE TABLE #result(
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  shift_id int,--id
			  shift_bh VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,--编号
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS,
			  shift_is_rest int,
				is_have_ot int null,
				is_created_ot int null
			)
			--同#result，用于检测放班处理
			CREATE TABLE #users(
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  shift_date DATE
			)

		-- 开启事务
			BEGIN TRAN

			--设置导入的月份
				select top 1 @yearMonth = rq from bd_schedule_receive where bh = @bh;

				--1过滤不存在的人

				insert into #returnTable(user_no,user_name,msg)
				select r.user_no,r.user_name,'该人员不存在'  from bd_schedule_receive r
				left join dt_user u on u.user_no = r.user_no
				where u.user_no is null  and r.bh = @bh;
				--移除不存在的人员
				delete from bd_schedule_receive where bh = @bh and user_no in (select user_no from #returnTable);


				--插入#scheduleTable
-- 				insert into #scheduleTable(user_serial,user_no,fh,banci_mc,col_name,date)
-- 				select u.user_serial,b.user_no,b.fh,b.banci_mc,b.col_name,b.rq from(
-- 				SELECT  a.user_no,
-- 							case when left(a.banci,3) = '[F]' then '[F]' else '[A]' end  as fh ,
-- 							case when left(a.banci,3) = '[F]' then substring(a.banci,4,len(a.banci))  else a.banci end  as banci_mc ,
-- 							dateadd(day,SUBSTRING(a.riqi, 2, LEN(a.riqi))-1,a.rq+'-01') as rq,
-- 							a.riqi as col_name
-- 					FROM bd_schedule_receive
-- 					UNPIVOT(banci FOR riqi IN(d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20,d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31))a
-- 					where bh = @bh) b
-- 					INNER JOIN dt_user u on u.user_no = b.user_no

				insert into #scheduleTable(user_serial,user_no,fh,banci_mc,col_name,date)
				select u.user_serial,b.user_no,b.fh,b.banci_mc,b.col_name,b.rq from(
					SELECT
							a.user_no,
							case when left(a.banci,3) = '[F]' then '[F]' else '[A]' end  as fh ,
							case when left(a.banci,3) = '[F]' then substring(a.banci,4,len(a.banci))  else a.banci end  as banci_mc ,
							dateadd(day,SUBSTRING(a.riqi, 2, LEN(a.riqi))-1,a.rq+'-01') as rq,
							a.riqi as col_name
						FROM (
							select
								user_no,
								user_name,
								rq,
								isnull(d1,'') as d1,
								isnull(d2,'') as d2,
								isnull(d3,'') as d3,
								isnull(d4,'') as d4,
								isnull(d5,'') as d5,
								isnull(d6,'') as d6,
								isnull(d7,'') as d7,
								isnull(d8,'') as d8,
								isnull(d9,'') as d9,
								isnull(d10,'') as d10,
								isnull(d11,'') as d11,
								isnull(d12,'') as d12,
								isnull(d13,'') as d13,
								isnull(d14,'') as d14,
								isnull(d15,'') as d15,
								isnull(d16,'') as d16,
								isnull(d17,'') as d17,
								isnull(d18,'') as d18,
								isnull(d19,'') as d19,
								isnull(d20,'') as d20,
								isnull(d21,'') as d21,
								isnull(d22,'') as d22,
								isnull(d23,'') as d23,
								isnull(d24,'') as d24,
								isnull(d25,'') as d25,
								isnull(d26,'') as d26,
								isnull(d27,'') as d27,
								isnull(d28,'') as d28,
								isnull(d29,'') as d29,
								isnull(d30,'') as d30,
								isnull(d31,'') as d31,
								bh
							from bd_schedule_receive
							where bh = @bh
						) x
						UNPIVOT( banci FOR riqi IN(x.d1,x.d2,x.d3,x.d4,x.d5,x.d6,x.d7,x.d8,x.d9,x.d10,
										x.d11,x.d12,x.d13,x.d14,x.d15,x.d16,x.d17,x.d18,x.d19,x.d20,
										x.d21,x.d22,x.d23,x.d24,x.d25,x.d26,x.d27,x.d28,x.d29,x.d30,x.d31))a
					)b
					INNER JOIN dt_user u on u.user_no = b.user_no
--
			 --2过滤所排班次不存在的人
			 insert into #returnTable(user_serial,user_no,user_name,msg)
				select DISTINCT r.user_serial,r.user_no,r.user_lname,'该人员所排班次有些不存在或班次中未定义上下班时段'  from
				(select u.user_serial,u.user_no,u.user_lname,s.banci_mc,zs.bh
				from #scheduleTable s
				INNER JOIN dt_user u on u.user_serial = s.user_serial
				LEFT JOIN zt_banci zb on zb.mc = s.banci_mc
				LEFT JOIN zt_shid zs on zb.bh = zs.parent and zs.lx=0
				) r
				where r.banci_mc != '' and r.bh is null

				--移除不存在的人员
				delete a from #scheduleTable a
				inner join #returnTable b on a.user_serial = b.user_serial

				--3过滤已封账的日期的人员
				insert into #returnTable(user_serial,user_no,user_name,msg)
				select DISTINCT u.user_serial,u.user_no,u.user_lname,'该人员已封账'
				from #scheduleTable s
				INNER JOIN dt_user u on u.user_serial = s.user_serial
				left JOIN bd_kq_accounts a on a.user_serial = s.user_serial and a.year =DATEPART(year, @yearMonth+'-01')  and a.month = DATEPART(month, @yearMonth+'-01')
				where isnull(a.lock_status,0)>0
				--移除不存在的人员
				delete a from #scheduleTable a
				inner join #returnTable b on a.user_serial = b.user_serial


				--4检测有无加班单
				--4检测之前是否有排班并且排班有生成的加班单（若该加班单存在，则该人本次全部日期都不能排班）
				insert into #returnTable(user_serial,user_no,user_name,msg)
				select DISTINCT u.user_serial,u.user_no,u.user_lname,'该时段存在排班加班单，不能重新排班'
				from #scheduleTable s
				INNER JOIN dt_user u on u.user_serial = s.user_serial
				inner join bd_kq_schedule_detail d on s.user_serial = d.user_serial and s.date = d.shift_date and d.is_created_ot = 1

				--移除不存在的人员
				delete a from #scheduleTable a
				inner join #returnTable b on a.user_serial = b.user_serial


				--5检测指定放班，前提是is_have_ot=0 即放班的排班的班次没有附带加班
				insert into #returnTable(user_serial,user_no,user_name,msg)
				select DISTINCT u.user_serial,u.user_no,u.user_lname,'该时段存在排班班次附带加班，不能放班'
				from #scheduleTable s
				INNER JOIN dt_user u on u.user_serial = s.user_serial
				inner join zt_banci bc on bc.mc = s.banci_mc and bc.is_delete=0
				INNER JOIN zt_shid sd on sd.parent = bc.bh and sd.lx = 1
				where s.fh='[F]'
				--移除不存在的人员
				delete a from #scheduleTable a
				inner join #returnTable b on a.user_serial = b.user_serial



				--插入到#result(不是导入的月份过滤掉，出现该情况的原因是，数据固定31个字段，标识该月的31天，
				--但不是所有的月份都有31天,这些不该存储的日期，被计算到下月，所以要把这些排除掉)
				insert into #result(user_serial,shift_id,shift_bh,col_name,value,date,year_month)
				select
							b.user_serial,
							zb.id,
							zb.bh,
							b.col_name,
							case when zb.bh is null then b.fh else b.fh+zb.bh+'|'+b.banci_mc end ,
							b.date,
							@yearMonth
				from #scheduleTable b
					left JOIN zt_banci zb on zb.mc = b.banci_mc and zb.is_delete=0
					where convert(varchar(7),b.date,120) =@yearMonth;


				--根据日历更新是否未休息日

				update r
					set r.value = case when (d.is_weekend=1 or d.is_statutory=1) then '[X]'+ SUBSTRING(r.value, 4,len(r.value)) else  r.value end,
							r.shift_is_rest = case when (d.is_weekend=1 or d.is_statutory=1) then 1 else  0 end
				from #result r
				INNER JOIN dt_user u on r.user_serial = u.user_serial
				INNER JOIN bd_kq_work_calendar_detail d on d.parent_id = u.user_work_calendar and r.date = d.work_date




				--处理	is_have_ot,is_created_ot
				--班次有加班时段，则is_have_ot = 1 ，is_created_ot=0
				--班次没有加班字段 则is_have_ot = 0 ，is_created_ot=0
				update s
					set s.is_have_ot = case when isnull(sd.bh,0)=0 then (case when (isnull(bc.id,0)!=0 and s.shift_is_rest=1) then 1 else 0 end) else 1 end ,
							s.is_created_ot = 0
				from #result s
				left join zt_banci bc on bc.id = s.shift_id
				left JOIN zt_shid sd ON bc.bh = sd.parent and sd.lx=1

				--删除旧数据
				--删除kt_paiba表中的相关记录
				delete k from  kt_paiba k
				INNER JOIN (select DISTINCT user_serial from #result) b on b.user_serial = k.user_serial
				where k.rq = @yearMonth;
				-- 删除排班纵向数据表
				delete a
				from bd_kq_schedule_detail a
				inner join #result b on a.user_serial = b.user_serial and convert(varchar(7) ,a.shift_date,120) = @yearMonth

				--导入中有可能存在该人整个月都没有排班，认定为本月要移除排班，则在删除旧数据之后，要过滤掉这些人，剩下的采取生成新数据
				--先将变动的人员和日期信息保存副本，检测放班使用
				insert into #users select user_serial,date from #result;
				--过滤
				delete from #result where value = '[A]' and shift_id  is null

				-- 将插入的数据存入到临时排班表中
				-- 下面两段代码将竖着的数据转为了横着的数据
				INSERT INTO kt_paiba (user_serial, rq, gly_no, year, month)
				SELECT distinct a.user_serial,@yearMonth,@operator,YEAR(date),MONTH(date)
				FROM #result a


				-- 循环拼接需要更新临时表中的字段
				-- 查询（循环）拼接数据（竖着循环）
				DECLARE @update_insert VARCHAR(MAX)=''
				SELECT @update_insert+='UPDATE kt_paiba SET '+col_name+'='''+isnull(value,'')+''',gly_no = '''+@operator+''' WHERE rq='''+year_month+''' AND user_serial='+
					CAST(a.user_serial AS VARCHAR(10))+';'
				FROM #result a

				-- 执行拼接的语句
				EXEC(@update_insert)

				-- 插入数据详情表
				insert into bd_kq_schedule_detail(user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,
				shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,
				night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest,is_have_ot,is_created_ot,update_id,update_time)
				select a.user_serial,a.shift_id,a.value,a.date,isnull(c.sbsj,0),isnull(c.xbsj,0),isnull(c.sbks,0),isnull(c.xbjs,0),isnull(c.cdsj,0),isnull(c.ztsj,0),
				isnull(c.bjcd,0),isnull(c.bjzt,0),isnull(c.alld,0),isnull(c.allt,0),isnull(c.is_enjoyment_subsidized,0),isnull(c.middle_shift_start_time,0),
				isnull(c.middle_shift_end_time,0),isnull(c.night_shift_start_time,0),isnull(c.night_shift_end_time,0),isnull(repast_start_time,0),isnull(c.repast_end_time,0),
				isnull(repast_start_time2,0),isnull(c.repast_end_time2,0),a.shift_is_rest,is_have_ot,is_created_ot,@operator,getdate()
				from #result a
				inner join zt_banci b on a.shift_id = b.id
				inner join zt_shid c on b.bh = c.parent
				where c.lx = 0



				--检测放班
				--创建一个游标
				declare my_cursor cursor for select user_serial,shift_date from #users
				--变量
				declare @date date;
				declare @user_serial varchar(200);
				--打开游标
				open my_cursor
				fetch next from my_cursor into @user_serial,@date  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
				while @@FETCH_STATUS=0 begin --假如检索到了数据继续执行
					--1调用放班操作请假单存储过程
					--参数：@user_serial  ，@date
					EXEC bd_proc_schedule_fb_leave @user_serial,@date,@operator

					fetch next from my_cursor into @user_serial,@date --获取下一条数据并赋值给变量
				end--关闭释放游标
				close my_cursor
				deallocate my_cursor


				--返回错误信息
				select id,user_no as userNo,user_name as userName,msg from #returnTable;

				DECLARE @user_serial_str varchar(max)=''
				DECLARE @begin_date varchar(10)
				DECLARE @end_date varchar(10)

				select @user_serial_str = @user_serial_str + CAST(u.user_serial AS VARCHAR(100)) + ',' from(
				SELECT DISTINCT user_serial FROM #result) u

				SET @user_serial_str = SUBSTRING(@user_serial_str,0, LEN(@user_serial_str))

				set @begin_date = @yearMonth+'-01';
				set @end_date = convert(varchar(10),EOMONTH(@yearMonth+'-01'),120);

				-- 调用考勤计算(排班4)
				EXEC bd_proc_timecard_extend @begin_date,@end_date,@user_serial_str,4

				set @result = @@ROWCOUNT;

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END;
			RAISERROR (@errStr, 16, 1);
		END CATCH


-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
go
